﻿<###################################################################################################################################
 # X509Certificate_Tools.ps1 SQL SERVER CERTIFICATE REQUIREMENTS	• The Common Name (CN) in the Subject property of the certificate must be the same as the fully qualified domain name (FQDN) of the server computer.
	• It must be issued for server authentication so the Enhanced Key Usage property of the certificate should include 'Server Authentication (1.3.6.1.5.5.7.3.1)'.
	• It must be created by using the KeySpec option of 'AT_KEYEXCHANGE'.Set-Location Cert:\LocalMachine\My
$c= Get-ChildItem | ? {$_.Subject -eq ((Get-WmiObject Win32_ComputerSystem) | % {"CN=$($_.DNSHostName).$($_.Domain)"})}
$eku= $c.Extensions | ? {$_.GetType().Name -eq 'X509EnhancedKeyUsageExtension'};
$eku.EnhancedKeyUsages | ? {$_.FriendlyName -eq 'Server Authentication' -or $_.FriendlyName -eq 'Client Authentication'} | % {$_}
================================================================================Clear-Host

[string]$Subject= 'CN=bba-phl-sqlprod.bbaaviation.net'

[switch]$CommonName= $true
[switch]$ServerAuthentication= $true	# 1.3.6.1.5.5.7.3.1
[switch]$ClientAuthentication= $false	# 1.3.6.1.5.5.7.3.2

$Go4Server= $false;
$Go4Client= $false;

Set-Location Cert:\LocalMachine\My;

 
foreach ($Certificate in (Get-ChildItem | ? {($_.Subject -eq $Subject) -or ($CommonName -eq $false)})) {$Certificate.Subject} 

foreach ($Certificate in (Get-ChildItem | ? {($_.Subject -eq $Subject) -or ($CommonName -eq $false)})) {
    Write-Output $Certificate.Subject;
    $eku= $Certificate.Extensions | ? {$Certificate.GetType().Name -eq 'X509EnhancedKeyUsageExtension'};
    if ($eku -ne $null) {
        $eku.EnhancedKeyUsages.Value | % {
            if ($_ -eq '1.3.6.1.5.5.7.3.1') {$Go4Server= $true; 'Go4Server!'}
            if ($_ -eq '1.3.6.1.5.5.7.3.2') {$Go4Client= $true; 'Go4Client'}
        }
        if (($ServerAuthentication -eq $false -or $Go4Server -eq $true) -and ($ClientAuthentication -eq $false -or $Go4Client -eq $true)) {$Certificate}
    }
}
 #>Set-StrictMode -Version Latest;Set-Variable -Scope Global -Option Constant -Name GetCertificateByThumbprint -Value {param ([string] $Thumbprint); Get-ChildItem -Path 'Cert:\LocalMachine\My' | ? {$_.Thumbprint -eq $Thumbprint}};
<##################################################################################################################################>
function Get-X509Certificate {<#
.SYNOPSIS
	what is it?

.DESCRIPTION
	Collect details on installed X509 certificates.

.PARAMETER ComputerName

.PARAMETER DBAdminServer

.EXAMPLE

.INPUTS
	System.String

.OUTPUTS
	None.

.NOTES
##>
[CmdletBinding()] Param (

    [Parameter (ValueFromPipeline= $True)]
    [string[]]
    $ComputerName= $ENV:ComputerName
,
    [Parameter]
    [switch]
    $CommonName
,
    [Parameter]
    [switch]
    $ServerAuthentication
)

BEGIN {};

PROCESS { $ComputerName |% {
    Write-Verbose $_;
    if ((Test-Connection -ComputerName $_ -Quiet) -eq $True) {

        # open the certificate store
        $Store= New-Object System.Security.Cryptography.X509Certificates.X509Store("\\$_\My",'LocalMachine');
        try {$Store.Open('OpenExistingOnly,ReadOnly')}
        catch {Write-Warning "$_ — $($_.Exception.Message)"};

        # read the certificates (may be zero)
        $Store.Certificates |% {
            Write-Verbose "$($_.Thumbprint) | $($_.NotAfter) | $($_.Subject)";
        }

        # cleanup
        try {$Store.Close()} catch {} finally {$Store= $null};

    } else {Write-Warning "$_ — Unreachable"};

}};

END {};
};



<###################################################################################################################################
 # HT: http://stackoverflow.com/questions/20852807/setting-private-key-permissions-with-powershell
 #>
function Set-X509CertificateACL {[CmdletBinding()] Param (

    [parameter (Mandatory= $True, ValueFromPipeline= $True)]
    [PSCustomObject]
    $Manifest)

BEGIN {
    $SetSQLCertificateACL= {
        Param ([string]$InstanceName, [string] $Account, [System.Management.Automation.ActionPreference] $VerbosePreference);
        $Thumbprint= $Certificate= $null;

        # get the SQL Server SSL certificate thumbprint
        $InstanceRoot= ((Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').PSObject.Properties | ? {$_.Name -eq $InstanceName}).Value;
        $Thumbprint= ((Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$InstanceRoot\MSSQLServer\SuperSocketNetLib").PSObject.Properties | ? {$_.Name -eq 'Certificate'}).Value;

        # fetch the certificate from the certificate store using the thumbprint
        if (-not [string]::IsNullOrEmpty($Thumbprint)) {
            $Store= New-Object System.Security.Cryptography.X509Certificates.X509Store("\\$env:COMPUTERNAME\My",'LocalMachine');
            $Store.Open('OpenExistingOnly,ReadWrite');
            $Certificate= $Store.Certificates | ? {$_.Thumbprint -eq $Thumbprint};
        };

        # modify the certificate file ACL; this is the same as Certificate Manager's "Manage Private Keys..."
        if ($Certificate -ne $null) {
            $KeyFullPath= $env:ProgramData + '\Microsoft\Crypto\RSA\MachineKeys\' + $Certificate.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName;
            $AccessRule= New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule -ArgumentList @($Account,'Read','Allow');
            $acl= Get-Acl -Path $keyFullPath;
            $acl.AddAccessRule($AccessRule);
            Set-Acl -Path $KeyFullPath -AclObject $acl;
        };
    };
};

PROCESS { $Manifest |% {
    $ServerInstance= $_.Instance;
    $Account= $_.Account;
    if ($ServerInstance.Contains('\')) {
        $ComputerName= [string]$ServerInstance.Split('\')[0];
        $InstanceName= [string]$ServerInstance.Split('\')[1];
    } else {
        $ComputerName= [string]$ServerInstance;
        $InstanceName= [string]'MSSQLServer';
    };
    Write-Verbose "$ServerInstance";
    Invoke-Command -ComputerName $ComputerName -ScriptBlock $SetSQLCertificateACL -ArgumentList $InstanceName,$Account,$VerbosePreference;
}};

END {};
};


<##################################################################################################################################>
Write-Verbose "X509Certificate_Tools Loaded";